package com.yuyou.fn.educational.mapper;

import com.github.wens.mybatisplus.mapper.AutoMapper;
import com.yuyou.fn.educational.entity.TeacherRemuneration;
import com.yuyou.fn.educational.vo.ClassRemunerationRankVo;
import com.yuyou.fn.educational.vo.ClassTimeRankVo;
import com.yuyou.fn.educational.vo.StudentAmountRankVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 *
 * TeacherRemuneration 表数据库控制层接口
 *
 */
public interface TeacherRemunerationMapper extends AutoMapper<TeacherRemuneration,Long> {


    @Select("select (@rank:=@rank+1 ) rankNo , c.* from ( select  sum( a.amount ) as amount , a.teacher_id teacherId   from teacher_remuneration a where a.area_team_id = #{areaTeamId} and a.period_id = #{periodId} group by a.teacher_id order by amount desc ) c , (select @rank :=0)  b ")
    List<ClassRemunerationRankVo> classRemunerationRank(@Param("areaTeamId") Long areaTeamId, @Param("periodId") Long periodId);

    @Select("select (@rank:=@rank+1 ) rankNo ,c.* from ( select  sum( a.class_time ) as classTime , a.teacher_id teacherId   from teacher_remuneration a where a.area_team_id = #{areaTeamId} and a.period_id = #{periodId} group by a.teacher_id  order by classTime desc) c , (select @rank :=0)  b")
    List<ClassTimeRankVo> classTimeRank(@Param("areaTeamId") Long areaTeamId, @Param("periodId") Long periodId );

    @Select("select d.* from ( select (@rank:=@rank+1 ) rankNo , c.* from ( select  sum( a.amount ) as amount , a.teacher_id teacherId   from teacher_remuneration a where a.area_team_id = #{areaTeamId} and a.period_id = #{periodId} group by a.teacher_id order by amount desc ) c , (select @rank :=0)  b ) d where d.teacherId = #{teacherId}")
    ClassRemunerationRankVo myClassRemunerationRank(@Param("areaTeamId") Long areaTeamId, @Param("periodId") Long periodId, @Param("teacherId") Long teacherId);

    @Select("select d.* from (select (@rank:=@rank+1 ) rankNo ,c.* from ( select  sum( a.class_time ) as classTime , a.teacher_id teacherId   from teacher_remuneration a where a.area_team_id = #{areaTeamId} and a.period_id = #{periodId} group by a.teacher_id  order by classTime desc ) c , (select @rank :=0)  b ) d where d.teacherId = #{teacherId}")
    ClassTimeRankVo myClassTimeRank(@Param("areaTeamId") Long areaTeamId, @Param("periodId") Long periodId, @Param("teacherId") Long teacherId);

    @Select("select d.* from ( select (@rank:=@rank+1 ) rankNo ,c.* from ( select  sum( a.student_amount ) as studentAmount , a.teacher_id teacherId   from teacher_remuneration a where a.area_team_id = #{areaTeamId} and a.period_id = #{periodId} group by a.teacher_id order by studentAmount desc ) c , (select @rank :=0)  b ) d where d.teacherId = #{teacherId}")
    StudentAmountRankVo myStudentAmountRank(@Param("areaTeamId")Long areaTeamId, @Param("periodId")Long periodId, @Param("teacherId")Long teacherId);

    @Select("select (@rank:=@rank+1 ) rankNo ,c.* from ( select  sum( a.student_amount ) as studentAmount , a.teacher_id teacherId   from teacher_remuneration a where a.area_team_id = #{areaTeamId} and a.period_id = #{periodId} group by a.teacher_id order by studentAmount desc ) c , (select @rank :=0)  b ")
    List<StudentAmountRankVo> studentAmountRank(@Param("areaTeamId") Long areaTeamId, @Param("periodId") Long periodId);

}